package com.example.demo.util;

import com.example.demo.entity.Exam;
import com.example.demo.entity.StuExternal;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;

public class OutUtil {
    public  void exportExcel(HttpServletResponse response, List<Exam> data, Map<Integer,List<StuExternal>> map) {
        String[] str={"学号", "姓名", "学院", "专业","第一学期绩点","第一学期学分",
                "第二学期绩点","第二学期学分","第三学期绩点","第三学期学分","第四学期绩点","第四学期学分",
                "第五学期绩点","第五学期学分","第六学期绩点","第六学期学分","第7学期绩点","第7学期学分",
                "绩点", "英语四级","计算机等级", "总计学分","是否达到毕业标准", "是否达到授位标准"};
        try {
            //实例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个Excel表单，参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //设置表头
            setTitle(workbook, sheet, str);
            //设置单元格并赋值
            setData(sheet, data,map);
            String filename=new String("1111.xls".getBytes(), StandardCharsets.UTF_8);
            //设置浏览器下载
            setBrowser(response, workbook,filename );
            System.out.println("导出解析成功!");
        } catch (Exception e) {
            System.out.println("导出解析失败!");
            e.printStackTrace();
        }
    }

    /**
     * setTitle设置标题
     * */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //创建表头名称
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            System.out.println("导出时设置表头失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setData
     * 功能：表格赋值
     */
    private static void setData(HSSFSheet sheet, List<Exam> data,Map<Integer,List<StuExternal>> map) {
        try{
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < 24; j++) {
                    if (j == 0) row.createCell(j).setCellValue(data.get(i).getId());
                    else if (j == 1) row.createCell(j).setCellValue(data.get(i).getName());
                    else if (j == 2) row.createCell(j).setCellValue(data.get(i).getCollege());
                    else if (j == 3) row.createCell(j).setCellValue(data.get(i).getMajor());
                    else if (j == 4) {
                        if(map.get(data.get(i).getId()).get(0)!=null)
                        row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(0).getGredit());
                        else row.createCell(j).setCellValue(2.0);
                    } else if (j == 5) {
                        if(map.get(data.get(i).getId()).get(0)!=null)
                        row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(0).getCredit());
                        else row.createCell(j).setCellValue(15);
                    } else if (j == 6) {
                        if(map.get(data.get(i).getId()).get(1)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(1).getGredit());
                        else row.createCell(j).setCellValue(2.0);
                    } else if (j == 7) {
                        if(map.get(data.get(i).getId()).get(1)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(1).getCredit());
                        else row.createCell(j).setCellValue(15);
                    } else if (j == 8) {
                        if(map.get(data.get(i).getId()).get(2)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(2).getGredit());
                        else row.createCell(j).setCellValue(2.0);
                }else if(j==9) {
                        if(map.get(data.get(i).getId()).get(2)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(2).getCredit());
                        else row.createCell(j).setCellValue(15);
                    }else if(j==10)  {
                        if(map.get(data.get(i).getId()).get(3)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(3).getGredit());
                        else row.createCell(j).setCellValue(2.0);
                }else if(j==11)  {
                        if(map.get(data.get(i).getId()).get(3)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(2).getCredit());
                        else row.createCell(j).setCellValue(15);
            }   else if(j==12)  {
                        if(map.get(data.get(i).getId()).get(4)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(4).getGredit());
                        else row.createCell(j).setCellValue(2.0);
        }       else if(j==13)  {
                        if(map.get(data.get(i).getId()).get(4)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(4).getCredit());
                        else row.createCell(j).setCellValue(15);
    }           else if(j==14)  {
                        if(map.get(data.get(i).getId()).get(5)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(5).getGredit());
                        else row.createCell(j).setCellValue(2.0);
}               else if(j==15)  {
                        if(map.get(data.get(i).getId()).get(5)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(5).getCredit());
                        else row.createCell(j).setCellValue(15);
        }                   else if(j==16) {
                        if(map.get(data.get(i).getId()).get(6)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(6).getGredit());
                        else row.createCell(j).setCellValue(2.0);
        }           else if(j==17)  {
                        if(map.get(data.get(i).getId()).get(6)!=null)
                            row.createCell(j).setCellValue(map.get(data.get(i).getId()).get(6).getCredit());
                        else row.createCell(j).setCellValue(15);
        }           else if(j==18)  {
                        row.createCell(j).setCellValue(data.get(i).getGredit());
        }           else if(j==19)  {
                        row.createCell(j).setCellValue(data.get(i).getEnglishTestFour());
                    }
                  else if(j==20)  {
                      row.createCell(j).setCellValue(data.get(i).getComputerLevel());
                    }
              else if(j==21)  {
                    row.createCell(j).setCellValue(data.get(i).getCredit());
        }           else if(j==22)  {
                  row.createCell(j).setCellValue(data.get(i).getIspass());
                    }
                 else if(j==23)  {
                     row.createCell(j).setCellValue(data.get(i).getIspassg());
                    }
                }
                rowNum++;
            }
            System.out.println("表格赋值成功！");
        }catch (Exception e){
            System.out.println("表格赋值失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setBrowser
     * 功能：使用浏览器下载
     */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            //response.reset();
            //设置response的Header
           // response.addHeader("Access-Control-Allow-Origin","*");
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            System.out.println("设置浏览器下载成功！");
        } catch (Exception e) {
            System.out.println("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }
}
